package com.spt.ws.dao.impl;


import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.spt.core.Property;
import com.spt.fjj.service.models.common.AnnotationCommonService;
import com.spt.ws.dao.IDataclean;
import org.springframework.util.ResourceUtils;


/**
 * dataclean  数据清理后台定时任务
 *
 */
@Service
public class dataCleanImpl extends AnnotationCommonService implements IDataclean {


	
	private static Logger log = LogManager.getLogger("com.spt.ws.dao.impl.dataCleanImpl");
	private static DecimalFormat df = new DecimalFormat("#,###");


	
	
	/*
	 * 运单数据，日志数据删除
	 * 删除七天前的  bgfjj_tajfj bgfjj_tyjgk
	 * @see com.spt.ws.dao.IDataclean#datadelete()
	 */
	
	@Transactional
    @Override
    public void datadelete() throws Exception {
        log.info(" 开始运单数据，日志数据删除  ");
        try {   
            String waybillkeepday = "";
            waybillkeepday  = Property.getProperty("logandyundan.keepdays","30");
            log.info("waybillkeepday:   " + waybillkeepday);
            String closeDate = this._querySingle("   select to_char(sysdate - ?,'yyyy/mm/dd') as  from dual",waybillkeepday);
            
            String kccloseDate = this._querySingle(" select to_char(sysdate - ?,'yyyymmdd') as kccloseDate from dual",waybillkeepday);
            log.info("开始清理 bgfjj_tajfj 表数据");
            int num = 0;
            do {
                num = this._update("delete from bgfjj_tajfj where to_char(to_date(ajsj,'yyyy/fmmm/fmdd hh24:mi:ss'),'yyyy/mm/dd') < ?  and rownum < 10001",closeDate);
            }while (num == 10000);
            
            log.info("开始清理 bgfjj_tyjgk 表数据");
            do {
                num = this._update("delete from bgfjj_tyjgk where kcsj < ?  and rownum < 10001",kccloseDate);
            }while (num == 10000);
            
            log.info("清理完成");
        } catch (RuntimeException e) {
            log.info(e.getMessage());
        }   
    }
	
	
	

    @Override
    public void cleanWrokData(){
        int num = 0;
        int retryTimes = 100;
        
        String delnumStr = Property.getProperty("del.Num.OneTime","10000");
        int delnum = this.toNumber(delnumStr);
        if(delnum == 0 ) {
            delnum = 10000;
        }
        log.info("delnum:   " + delnum);
        String closeDate = this._querySingle("select to_char(sysdate - 1,'yyyy-mm-dd hh24:mi:ss') from dual");
        String insert_bgfjj_ffyjxxb_his = "insert into bgfjj_ffyjxxb_his nologging (BZ, CJDM, GBCZY, GJTH, HEIGHT, ID, JSBZ, LENGTH, LGSJ, LXGK, SBID, SCBZ, SJZL, "
                + "WIDTH, WLGKHM, XCBH, YJTM, YJZL, YLDM,XCBH1) select BZ, CJDM, GBCZY, GJTH, HEIGHT, ID, JSBZ, LENGTH, LGSJ, LXGK, SBID, SCBZ, SJZL, "
                + "WIDTH, WLGKHM, XCBH, YJTM, YJZL, YLDM,XCBH1 from bgfjj_ffyjxxb where lgsj <　? ";
        try {
            num = this._update(insert_bgfjj_ffyjxxb_his,closeDate);
            log.info("Move bgfjj_ffyjxxb table, " + df.format(num) + " done!");
            String clean_bgfjj_ffyjxxb = "delete from bgfjj_ffyjxxb where  lgsj < ? and rownum <= " + delnum; 
            retryTimes = 100;
            do {
                try{
                    num = this._delete(clean_bgfjj_ffyjxxb,closeDate);
                    retryTimes = 100;
                    log.info("Delete bgfjj_ffyjxxb table, " + df.format(num) + " done!");
                }catch (Exception e) {
                    retryTimes--;
                    log.error(e.getMessage());
                }
                if(retryTimes == 0) {
                    log.error("Delete bgfjj_ffyjxxb table 删除多次失败，放弃该表操作");
                    break;
                }
            }while (num == delnum);
        } catch (Exception ex) {
            log.error(ex.getMessage());
        }
        
        
        String insert_bgfjj_trace_his = "insert into bgfjj_trace_his nologging (BZ, CAR, CSBZ, GJFS, GJSJ, GJTHM, HEIGHT, ID, JSBZ, LENGTH, LXGK, SMCZY, SMQH, "
                + "SMSJ, WEIGHT, WIDTH, WLGKH1, WLGKH2, WLGKH3, WLGKH4, YJTM, YJZL, YLDM, ZT,CAR1) "
                + "select BZ, CAR, CSBZ, GJFS, GJSJ, GJTHM, HEIGHT, ID, JSBZ, LENGTH, LXGK, SMCZY, SMQH, "
                + "SMSJ, WEIGHT, WIDTH, WLGKH1, WLGKH2, WLGKH3, WLGKH4, YJTM, YJZL, YLDM, ZT,CAR1 "
                + "from   bgfjj_trace where  gjsj < ? ";
        try {
            num = this._update(insert_bgfjj_trace_his,closeDate);
            log.info("Move bgfjj_trace table, " + df.format(num) + " done!");
            String clean_bgfjj_trace = "delete from   bgfjj_trace where  gjsj < ? and rownum <= " + delnum; 
            retryTimes = 100;
            do {
                try{
                    num = this._delete(clean_bgfjj_trace,closeDate);
                    log.info("Delete bgfjj_trace table, " + df.format(num) + " done!");
                    retryTimes = 100;
                }catch (Exception e) {
                    retryTimes--;
                    log.error(e.getMessage());
                }
                if(retryTimes == 0) {
                    log.error("Delete bgfjj_trace table 删除多次失败，放弃该表操作");
                    break;
                }
                
            }while (num == delnum);
        } catch (Exception ex) {
            log.error(ex.getMessage());
        }
        
        
        String insert_bgfjj_srgkztxxb_his = "insert into bgfjj_srgkztxxb_his nologging (CJDM, SCBZ, ID, SBID, SRGKJRSJ, SRYY, WLGKH, YJTM) "
                + "select CJDM, CSBZ, ID, SBID, SRGKJRSJ, SRYY, WLGKH, YJTM from bgfjj_srgkztxxb where  srgkjrsj < ? ";
        try {
            num = this._update(insert_bgfjj_srgkztxxb_his,closeDate);
            log.info("Move bgfjj_srgkztxxb table, " + df.format(num) + " done!");
            String clean_bgfjj_srgkztxxb="delete from bgfjj_srgkztxxb where  srgkjrsj < ? and rownum <= " + delnum;
            retryTimes = 100;
            do {
                try{
                    num = this._delete(clean_bgfjj_srgkztxxb,closeDate);
                    log.info("Delete bgfjj_srgkztxxb table, " + df.format(num) + " done!");
                    retryTimes = 100;
                }catch (Exception e) {
                    retryTimes--;
                    log.error(e.getMessage());
                }
                if(retryTimes == 0) {
                    log.error("Delete bgfjj_srgkztxxb table 删除多次失败，放弃该表操作");
                    break;
                }
                
            }while (num == delnum);
        } catch (Exception ex) {
            log.error(ex.getMessage());
        }
        
        
        
        String insert_bgfjj_xzsbxxb_his = "insert into bgfjj_xzsbxxb_his nologging (CARNO, ID, SBSJ, SBYY, WLGKH, YJTM) select CARNO, ID, SBSJ, SBYY, WLGKH, YJTM "
                + "from bgfjj_xzsbxxb where sbsj < ? ";
        try {
            num = this._update(insert_bgfjj_xzsbxxb_his,closeDate);
            log.info("Move bgfjj_xzsbxxb table, " + df.format(num) + " done!");
            String clean_bgfjj_xzsbxxb = "delete from bgfjj_xzsbxxb where sbsj < ? and rownum <= " + delnum;
            retryTimes = 100;
            do {
                try{
                    num = this._delete(clean_bgfjj_xzsbxxb,closeDate);
                    log.info("Delete bgfjj_xzsbxxb table, " + df.format(num) + " done!");
                    retryTimes = 100;
                }catch (Exception e) {
                    retryTimes--;
                    log.error(e.getMessage());
                }
                if(retryTimes == 0) {
                    log.error("Delete bgfjj_xzsbxxb table 删除多次失败，放弃该表操作");
                    break;
                }
            }while (num == delnum);
        } catch (Exception ex) {
            log.error(ex.getMessage());
        }
        
        
        
        String insert_bgfjj_tzbsjxxb_his = "insert into bgfjj_tzbsjxxb_his nologging (sbid, yjtm, sjzl, gjth, gbczy, xcbh, lgsj, yldm, lxgk, yjzl, "
                + "wlgkhm, cjdm, bz, length, width, height, id, scbz, jsbz, xcbh1, bxlgbz, pch, zbscbz, xjz ) "
                + "select sbid, yjtm, sjzl, gjth, gbczy, xcbh, lgsj, yldm, lxgk, yjzl, wlgkhm, cjdm, bz, length, "
                + "width, height, id, scbz, jsbz, xcbh1, bxlgbz, pch, zbscbz, xjz "
                + "from bgfjj_tzbsjxxb where lgsj < ? ";
        try {
            num = this._update(insert_bgfjj_tzbsjxxb_his,closeDate);
            log.info("Move bgfjj_tzbsjxxb table, " + df.format(num) + " done!");
            String clean_bgfjj_tzbsjxxb = "delete from bgfjj_tzbsjxxb  where lgsj < ? and rownum <= " + delnum;
            retryTimes = 100;
            do {
                try{
                    num = this._delete(clean_bgfjj_tzbsjxxb,closeDate);
                    log.info("Delete bgfjj_tzbsjxxb table, " + df.format(num) + " done!");
                    retryTimes = 100;
                }catch (Exception e) {
                    retryTimes--;
                    log.error(e.getMessage());
                }
                if(retryTimes == 0) {
                    log.error("Delete bgfjj_tzbsjxxb table 删除多次失败，放弃该表操作");
                    break;
                }
            }while (num == delnum);
        } catch (Exception ex) {
            log.error(ex.getMessage());
        }
        
        
        
        
    }

    @Override
    public void cleanHisData() throws Exception {
        String delnumStr = Property.getProperty("del.Num.OneTime","10000");
        log.info("delnumStr is " + delnumStr);
        int delnum = this.toNumber(delnumStr);
        if(delnum == 0 ) {
            log.info("delnum is null ");
            delnum = 10000;
        }
        String keepDayStr = Property.getProperty("history.data.keepdays","30");
        log.info("keepDayStr is " + keepDayStr);
        int keepDays = this.toNumber(keepDayStr);
        if(keepDays == 0) {
            log.info("keepDays is null ");
            keepDays = 30;
        }
        log.info("keepDays:   " + keepDays);
        int num = 0;
        String closeDate = this._querySingle("select to_char(sysdate - ?,'yyyy-mm-dd hh24:mi:ss') from dual",keepDays);
        
        String clean_bgfjj_ffyjxxb_his = "delete from bgfjj_ffyjxxb_his where lgsj < ?  and rownum <= " + delnum;
        do {
            num = this._delete(clean_bgfjj_ffyjxxb_his,closeDate);
            log.info("Delete bgfjj_ffyjxxb_his table, " + df.format(num) + " done!");
        }while (num == delnum);
        
        
        String clean_bgfjj_trace_his = "delete from bgfjj_trace_his where gjsj < ?  and rownum <= " + delnum;
        do {
            num = this._delete(clean_bgfjj_trace_his,closeDate);
            log.info("Delete bgfjj_trace_his table, " + df.format(num) + " done!");
        }while (num == delnum);
        
        
        String clean_bgfjj_srgkztxxb_his = "delete from bgfjj_srgkztxxb_his where srgkjrsj < ?  and rownum <= " + delnum;
        do {
            num = this._delete(clean_bgfjj_srgkztxxb_his,closeDate);
            log.info("Delete bgfjj_srgkztxxb_his table, " + df.format(num) + " done!");
        }while (num == delnum);
        
        
        String clean_bgfjj_xzsbxxb_his = "delete from bgfjj_xzsbxxb_his where sbsj < ?  and rownum <= " + delnum;
        do {
            num = this._delete(clean_bgfjj_xzsbxxb_his,closeDate);
            log.info("Delete bgfjj_xzsbxxb_his table, " + df.format(num) + " done!");
        }while (num == delnum);
        
        
        String clean_bgfjj_tzbsjxxb_his = "delete from bgfjj_tzbsjxxb_his where lgsj < ?  and rownum <= " + delnum;
        do {
            num = this._delete(clean_bgfjj_tzbsjxxb_his,closeDate);
            log.info("Delete bgfjj_tzbsjxxb_his table, " + df.format(num) + " done!");
        }while (num == delnum);
        
        
        String clean_tgkdyfpb = "delete from tgkdyfpb where scsj < ?  and rownum <=" + delnum;
        do {
            num = this._delete(clean_tgkdyfpb,closeDate);
            log.info("Delete tgkdyfpb table, " + df.format(num) + " done!");
        }while (num == delnum);
        
        String clean_bgfjj_tzbxxb = "delete from bgfjj_tzbxxb where ffsj < ? and rownum <= " + delnum;
        do {
            num = this._delete(clean_bgfjj_tzbxxb,closeDate);
            log.info("Delete bgfjj_tzbxxb table, " + df.format(num) + " done!");
        }while (num == delnum);
        
        String clean_tgkkyfpb = "delete from TGKDYFPB where scsj < ? and rownum <= " + delnum;
        do {
            num = this._delete(clean_tgkkyfpb,closeDate);
            log.info("Delete tgkkyfpb table, " + df.format(num) + " done!");
        }while (num == delnum);
    }




    @Override
    public void cleanData()  {
	    log.info("start service");
        Properties pro = new Properties();
        try {
            File file = ResourceUtils.getFile("classpath:dataclean.properties");
            FileInputStream in = new FileInputStream(file);
            pro.load(in);
        }catch(Exception e){
            log.error("读取清理配置文件出错，请检查配置文件是否存在！");
            return;
        }
        String tablesStr = pro.getProperty("clean.tables");
        int num;
        if(StringUtils.isBlank(tablesStr)){
            log.info("没有需要清理的数据表。");
            return;
        }
        String delnumStr = pro.getProperty("del.Num.OneTime","10000");
        int delnum = this.toNumber(delnumStr);
        if(delnum == 0 ) {
            delnum = 10000;
        }
        String retryTimesStr = pro.getProperty("clean.retryTimes","10000");
        int retryTimes = this.toNumber(retryTimesStr);
        if(retryTimes == 0 ) {
            retryTimes = 3;
        }

        String[] tablesArray = tablesStr.split(",");
        for(String tableName : tablesArray){        //tablename 为需要清理的表名
            log.info("开始清理 " + tableName + " ......");
            if(!isTableExist(tableName)) {
                log.error(tableName + " 表在数据库中不存在！");
                continue;
            }
            String dateCol = pro.getProperty(tableName + ".datecol");
            if(StringUtils.isBlank(dateCol)){
                log.info("    没有指定日期字段，放弃 " + tableName + " 表清理。");
                continue;
            }
            String dateFormat = pro.getProperty(tableName + ".dataformat");
            if(StringUtils.isBlank(dateFormat)){
                log.info("    没有指定日期格式，放弃 " + tableName + " 表清理。");
                continue;
            }

            //工作表保留期限
            String keepDays = pro.getProperty(tableName + ".keepdays");
            int keepDaysNum = this.toNumber(keepDays);
            if(keepDaysNum == 0) keepDays = "1";

            String backTableName = pro.getProperty(tableName + ".backtable");
            String workWhereClause = null;
            if(StringUtils.isNotBlank(backTableName)){
                if(!isTableExist(backTableName)) {
                    log.error(backTableName + " 表在数据库中不存在！");
                    continue;
                }

                //向备份表复制数据
                log.info("        开始向" + backTableName + " 表备份数据......");
                String backColumns = pro.getProperty(tableName + ".backcolumns");
                if(!this.checkColumns(tableName, backTableName, backColumns)){
                    log.error("  备份字段未通过校验，放弃 " + tableName + " 表的备份！！！！！！！");
                    continue;
                }
                StringBuilder backSql = new StringBuilder("insert into ");
                backSql.append(backTableName).append(" nologging ");
                if(StringUtils.isBlank(backColumns)) {
                    backSql.append( " select * from ");
                }else{
                    backColumns = StringUtils.join(backColumns.split(","),",");
                    backSql.append("(").append(backColumns).append(") ").append( " select ").append(backColumns).append(" from ");
                }
                backSql.append(tableName);

                try{
                    workWhereClause = this.getWhereClause(dateCol, dateFormat, keepDays);
                    backSql.append(workWhereClause);
                }catch(Exception e){
                    log.error("    准备复制数据sql语句时出现了错误，错误信息： " + e.getMessage());
                    log.error("    " + tableName + " 清理中断。");
                    continue;
                }
                try{
                    num = this._update(backSql.toString());
                    log.info("    " + tableName + " 共有 " + df.format(num) + " 条数据被复制到 " + backTableName + "， 开始清理 " + tableName + " 表....");
                }catch(Exception e){
                    log.error("    复制数据至备份表时出现错误，错误信息： " + e.getMessage());
                    log.error("    " + tableName + " 清理中断。");
                    continue;
                }

                log.info("        开始清理" + backTableName + " 表中的备份数据......");
                //备份表保留期限
                String backKeepDays = pro.getProperty(tableName + ".baktable.keepdays");
                int backKeepDaysNum = this.toNumber(backKeepDays);
                if(backKeepDaysNum == 0) backKeepDays = "30";
                //清理备份表sql
                StringBuilder cleanBackTableSql = new StringBuilder("delete from ");
                cleanBackTableSql.append(backTableName);
                try{
                    String whereClause = this.getWhereClause(dateCol, dateFormat, backKeepDays);
                    cleanBackTableSql.append(whereClause).append(" and rownum <= ").append(delnum);
                }catch(Exception e){
                    log.error("    准备sql语句时出现了错误，错误信息： " + e.getMessage());
                    log.error("    " + tableName + " 清理中断。");
                    continue;
                }
                this.cleanTable(cleanBackTableSql.toString(), backTableName, retryTimes, delnum);
            }
            //清理工作表
            StringBuilder cleanTableSql = new StringBuilder("delete from ");
            cleanTableSql.append(tableName);
            try{
                //String whereClause = this.getWhereClause(dateCol, dateFormat, keepDays);
                if(StringUtils.isBlank(workWhereClause)) workWhereClause = this.getWhereClause(dateCol, dateFormat, keepDays);
                cleanTableSql.append(workWhereClause).append(" and rownum <= ").append(delnum);
            }catch(Exception e){
                log.error("    准备清理工作表sql语句时出现了错误，错误信息： " + e.getMessage());
                log.error("    " + tableName + " 清理中断。");
                continue;
            }
            log.info("开始清理 " + tableName + " 表中数据......");
            this.cleanTable(cleanTableSql.toString(), tableName, retryTimes, delnum);
            log.info(tableName + "表清理完毕。");
        }
    }


    private void cleanTable(String sql, String tableName, int retryTimes, int  delNum){
        int num ,retryRemainTimes = retryTimes;
        do {
            try{
                num = this._delete(sql);
                retryRemainTimes = retryTimes;
                log.info("    " + tableName + " 成功清理 " + df.format(num) + " 条数据......");
            }catch (Exception e) {
                retryRemainTimes--;
                num = delNum;
                log.error("    " + tableName + " 清理时出现错误，错误信息：" + e.getMessage());
                log.error("    还会尝试" + retryRemainTimes + " 次。");
            }
            if(retryRemainTimes == 0) {
                log.error("    " + tableName + " 清理时失败次数过多，放弃清理！");
                break;
            }
        }while (num == delNum);
    }


    private String getWhereClause(String columnName, String dateFormat, String keepDays) throws Exception {
        StringBuilder whereClause = new StringBuilder(" where ");
        String keepDaysParam;
        if("date".equalsIgnoreCase(dateFormat)){
            whereClause.append(" to_char(").append(columnName).append(",'yyyymmddhh24miss') < '");
            keepDaysParam = this._querySingle("select to_char(sysdate - " + keepDays + ",'yyyymmddhh24miss') from dual");
        }else{
            whereClause.append(columnName).append(" < '");
            keepDaysParam = this._querySingle("select to_char(sysdate - " + keepDays + ",'" +  dateFormat + "') from dual");
        }
        whereClause.append(keepDaysParam).append("'");
        return whereClause.toString();
    }

    private boolean isTableExist(String tableName){
        String sql = "select count(*) from user_tables where table_name = UPPER(?)";
        try{
            String num = this._querySingle(sql, tableName);
            return "1".equals(num);
        }catch(Exception e){
            log.error("检查表出错，出错信息： " +  e.getMessage());
            return false;
        }
    }

    private boolean checkColumns(String tableName, String backTableName, String cols){
        StringBuilder tableCol = new StringBuilder("select * from USER_TAB_COLS where 1=1 ");
        StringBuilder backTableCol = new StringBuilder("select * from USER_TAB_COLS where 1=1 ");
        List<Map<String, Object>> liCols, liBackCols;
        List<String> params = Lists.newArrayList();
        //params.add(tableName);
        try {
            if (StringUtils.isNotBlank(cols)) {
                String[] columns = cols.split(",");
                tableCol.append(" and column_name in (");
                backTableCol.append(" and column_name in (");
                for (String column : columns) {
                    tableCol.append("upper(?), ");
                    backTableCol.append("upper(?), ");
                    params.add(column);
                }
                tableCol.deleteCharAt(tableCol.lastIndexOf(",")).append(") and table_name = upper(?) ");
                backTableCol.deleteCharAt(backTableCol.lastIndexOf(",")).append(") and table_name = upper(?) ");
                params.add(tableName);
                liCols = this._queryMultex(tableCol.toString(), params.toArray());
                params.remove(tableName);
                params.add(backTableName);
                liBackCols = this._queryMultex(backTableCol.toString(), params.toArray());
                if(columns.length != liCols.size()){
                    log.error(" 给定的备份字段与工作表中的字段不一致！");
                    return false;
                }
            }else{
                tableCol.append("  and table_name = upper(?) ");
                liCols = this._queryMultex(tableCol.toString(), tableName);
                backTableCol.append("  and table_name = upper(?) ");
                liBackCols = this._queryMultex(backTableCol.toString(), backTableName);
            }


            for(Map<String, Object> column : liCols){
                String colName = (String) column.get("COLUMN_NAME");
                String dataType = (String) column.get("DATA_TYPE");
                BigDecimal dataLength = (BigDecimal) column.get("DATA_LENGTH");
                String nullable = (String) column.get("NULLABLE");
                boolean hasCol = false;
                for(Map<String, Object> backColumn : liBackCols){
                    String backColName = (String) backColumn.get("COLUMN_NAME");
                    if(!colName.equals(backColName)) continue;
                    String bakDataType = (String) backColumn.get("DATA_TYPE");
                    if(!dataType.equalsIgnoreCase(bakDataType)) {
                        log.error(backColName + " 数据类型不同！");
                        return false;
                    }
                    BigDecimal backDataLength = (BigDecimal) backColumn.get("DATA_LENGTH");
                    if(dataLength.compareTo(backDataLength) == 1){
                        log.error(backColName + " 工作表中字段长度大于备份表！");
                        return false;
                    }
                    String backNullable = (String) backColumn.get("NULLABLE");
                    if("N".equalsIgnoreCase(backNullable) && !backNullable.equalsIgnoreCase(nullable)){
                        log.error(backColName + " 工作表中字段可以为空但备份表中不允许为空！");
                        return false;
                    }
                    hasCol = true;
                    break;
                }
                if(!hasCol) {
                    log.error("在备份表中没有 " + colName + "字段!");
                    return false;
                }
            }
        }catch (Exception e){
            log.error("检查表字段时出错！");
            return false;
        }
        return true;
    }
	
	int toNumber(String s) {
	    try{
	        int num = Integer.parseInt(s);
	        return num;
	    }catch(Exception e) {
	        log.error(e.getMessage());
	        return 0;
	    }
	}
}
